﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Windows.Forms;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
// 針對MS SQL

namespace Org.Ponylab.DotNetLab.EntLib.DataAccessLab
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        /// <summary>
        /// 新增一個與MS資料庫連線
        /// </summary>
        /// <param name="connString">選擇性參數，資料庫連線字串</param>
        /// <returns></returns>
        private SqlConnection CreateDbConnection( string connString = "" )
        {
            if ( string.IsNullOrEmpty( connString ) )
            {
                // 取得在app.config檔裡的connectionString
                connString = ConfigurationManager.ConnectionStrings[ "HandOnLabDB" ].ConnectionString;
            }

            SqlConnection msSqlConn = new SqlConnection( connString );
            return msSqlConn;
        }

        /// <summary>
        /// 開啟與資料庫的連線
        /// </summary>
        /// <returns></returns>
        private SqlConnection OpenSqlConnention()
        {
            SqlConnection conn = CreateDbConnection();
            try
            {
                conn.Open();
                return conn;
            }
            catch ( Exception ex )
            {
                throw new Exception( "無法開啟與資料庫連線" );
            }
        }

        /// <summary>
        /// 關閉與資料庫的連線
        /// </summary>
        /// <param name="sqlConnection">目前與資料庫縣中的連線</param>
        /// <returns>是否關閉成功</returns>
        private bool CloseSqlConnecntion( SqlConnection sqlConnection )
        {
            if ( sqlConnection == null )
            {
                throw new ArgumentNullException( "傳入的引數為null!" );
            }

            try
            {
                sqlConnection.Close();
                return true;
            }
            catch ( Exception ex )
            {
                return false;
            }
        }

        /// <summary>
        /// 執行T-SQL指令的方法
        /// </summary>
        /// <param name="sqlString">T-SQL指令字串</param>
        /// <returns>執行是否成功</returns>
        private bool ExecuteQuerySqlCommand( string sqlString )
        {
            if ( string.IsNullOrEmpty( sqlString ) )
            {
                return false;
            }

            SqlConnection conn = OpenSqlConnention();
            SqlCommand sqlCommand = new SqlCommand( sqlString, conn );
            sqlCommand.ExecuteNonQuery();

            return true;
        }

        /// <summary>
        /// 包裝查詢結果成一個DataSet物件
        /// </summary>
        /// <param name="sqlConn">資料庫連線</param>
        /// <param name="sqlString">T-SQL指令字串</param>
        /// <param name="tableName">資料表名稱</param>
        /// <returns>DataSet物件</returns>
        private DataSet GetResultData( SqlConnection sqlConn, string sqlString, string tableName = "Customers" )
        {
            if ( sqlConn == null || string.IsNullOrEmpty( sqlString ) )
            {
                throw new ArgumentNullException( "傳入的引數為null!" );
            }

            SqlDataAdapter adapter = new SqlDataAdapter( sqlString, sqlConn );

            DataSet ds = new DataSet();
            adapter.Fill( ds, tableName );
            return ds;
        }

        private bool InsertData( SqlConnection sqlConn, Dictionary<string, string> inputDatas )
        {
            string name    = string.Empty;
            string address = string.Empty;
            string city    = string.Empty;
            
            if ( inputDatas.ContainsKey( "name" ) ||
                 inputDatas.ContainsKey( "address" ) ||
                 inputDatas.ContainsKey( "city" ) )
            {
                name    = inputDatas[ "name" ];
                address = inputDatas[ "address" ];
                city    = inputDatas[ "city" ];
            }
            else
            {
                throw new ArgumentNullException( "傳入的引數為key值有錯誤!" );
            }

            try
            {
                //string sqlString =
                //        "INSERT INTO Customers" +
                //        "( Name, Address, City )" +
                //        string.Format( "VALUES('{0}', '{1}', '{2}')", name, address, city );

                string sqlString = @"INSERT INTO Customers( Name, Address, City )
                                        VALUES(@name, @address, @city)";

                SqlCommand sqlCommand = new SqlCommand( sqlString, sqlConn );

                // 插入參數
                sqlCommand.Parameters.Add( new SqlParameter( "@name"   , SqlDbType.NVarChar ) );
                sqlCommand.Parameters.Add( new SqlParameter( "@address", SqlDbType.NVarChar ) );
                sqlCommand.Parameters.Add( new SqlParameter( "@city", SqlDbType.NVarChar ) ).Value = city;
                //sqlCommand.Parameters.Add( "@city", SqlDbType.NVarChar ).Value = city;

                // 設定參數值
                sqlCommand.Parameters[ "@name" ].Value    = name;
                sqlCommand.Parameters[ "@address" ].Value = address;

                sqlCommand.ExecuteNonQuery();

                return true;
            }
            catch ( Exception ex )
            {
                return false;
            }
        }


        #region 事件處理常式

        private void btnLoadData_Click( object sender, EventArgs e )
        {
            // 利用Enterprise Library Container取得現在Database物件
            //Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>( "HandOnLabDB" );
            
            // 利用DatabaseFactory工廠取得Database物件
            Database db = DatabaseFactory.CreateDatabase( "HandOnLabDB" );

            DataSet ds            = db.ExecuteDataSet( CommandType.Text, "SELECT * FROM Customers" );
            DataTable dtCustomers = ds.Tables[ 0 ];
            dtCustomers.TableName = "Custromers Details";

            dgvCustomersDetails.DataSource = dtCustomers;
        }

        private void Form1_Load( object sender, EventArgs e )
        {
            // pass
        }

        private void btnLoad_Click( object sender, EventArgs e )
        {
            SqlConnection sqlConn = OpenSqlConnention();
            DataSet dsResult = GetResultData(
                sqlConn,
                "SELECT * FROM Customers",
                "Customers"
            );

            dgvCustomersDetails.DataSource = dsResult.Tables[ "Customers" ];

            if ( CloseSqlConnecntion( sqlConn ) )
            {
                MessageBox.Show( "成功關閉與資料庫的連線!" );
            }
        }

        private void btnCommit_Click( object sender, EventArgs e )
        {
            string inputName    = txtInserName.Text.Trim();
            string inputAddress = txtInsertAddress.Text.Trim();
            string inputCity    = txtInsertCity.Text.Trim();

            if ( string.IsNullOrEmpty( inputName ) || string.IsNullOrEmpty( inputAddress ) || string.IsNullOrEmpty( inputCity ) )
            {
                MessageBox.Show( "error" );
                return;
            }

            Dictionary<string, string> datas = new Dictionary<string, string>
            {
                { "name", inputName },
                { "address", inputAddress },
                { "city", inputCity }
            };

            SqlConnection sqlConn = OpenSqlConnention();
            bool isInsertSuccess = InsertData( sqlConn, datas );
            //string insertResultMsg = isInsertSuccess ? "insert success" : "insert fails";
            //MessageBox.Show( insertResultMsg );

            string insertResultMsg = string.Empty;
            if ( isInsertSuccess )
            {
                insertResultMsg = "insert success";
            }
            else
            {
                insertResultMsg = "insert fails";
            }

            MessageBox.Show( insertResultMsg );
        }

        #endregion
    }
}
